package com.scalar.chiptrack.adaptor.sitime.sales;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import jxl.read.biff.BiffException;

import com.scalar.ScalarException;
import com.scalar.chiptrack.adaptor.AdaptorUtils;
import com.scalar.chiptrack.adaptor.dao.AdaptorRunInfo;
import com.scalar.chiptrack.adaptor.utils.Parser;
import com.scalar.chiptrack.adaptor.utils.ParserFactory;
import com.scalar.chiptrack.adaptor.utils.ParserResult;
import com.scalar.chiptrack.adaptor.utils.XLSParserImpl;
import com.scalar.chiptrack.commons.Address;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DBXException;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.jobs.JobHandler;
import com.scalar.chiptrack.sales.parser.SalesParserDAO;
import com.scalar.chiptrack.sales.parser.SalesParserLineItem;
import com.scalar.chiptrack.users.notification.dao.NotificationDAO;
import com.scalar.chiptrack.utils.ConfigManager;
import com.scalar.chiptrack.utils.Logger;
import com.scalar.chiptrack.utils.Mailer;
import com.scalar.chiptrack.utils.StringUtils;

/**
 * User: Sujan
 * Date: Aug 1, 2008
 * Time: 4:32:56 PM
 */
public class SalesParser extends JobHandler
{
    private Logger m_parserLogger   = null;
    private String m_dataSourceDir  = null;
    private final String BACKLOG_SHEET = "Daily Backlog";

    public SalesParser() throws Exception
    {
        try
        {
            m_dataSourceDir = ConfigManager.getSalesBacklogPortingFileDir() ;
            if ( m_dataSourceDir != null )
            {
                AdaptorUtils.makeAllAdaptorDirs( m_dataSourceDir );

                m_parserLogger = AdaptorUtils.getDataParserLogger( m_dataSourceDir, "salesBacklog" );
                AdaptorUtils.setLogger( m_parserLogger );
            }
        }
        catch ( Exception e )
        {
            e.printStackTrace();
        }
    }
    // To be implemented by sub classes
    public void execute() throws Exception
    {
        boolean success = false;
        boolean fgPartporting = false;
        AdaptorUtils.printHeader("SALES BACKLOG PORTING", CONSTANT_PARSER );

        ArrayList<File> successFileList      = new ArrayList<File>();
        File doneDestDir = new File( m_dataSourceDir + "/done" );
        File dataoutDir  = new File( m_dataSourceDir );

        File[] fileList = dataoutDir.listFiles();

        if ( ( fileList == null ) || ( fileList.length <= 3 ) )
        {
            log( "No file found in directory: " + dataoutDir );
            throw new ScalarException( "\n+-----------No file found in directory " + dataoutDir + "-----\n\n" );
        }
        HashMap<String, String> errorsMap   = new HashMap<String, String>();
        String portingStatusMsg = null;
        ArrayList<SalesParserLineItem> lineItemList = null;
        for ( int p = 0; p < fileList.length; p++ )
        {
            try
            {
                //For CREATING the PARTS IN PRODUCT TABLE
                if ( fileList[p].getName() != null && fileList[p].getName().trim().startsWith( SalesAdaptor.FILE_NAME_FG_PARTS ) )
                {
                    errorsMap = createFGParts( fileList[p].getAbsolutePath() ) ;

                    successFileList.add( fileList[p] );
                }
                fgPartporting = true;
            }
            catch (ScalarException e)
            {
                fgPartporting = false;
                log ( "Exception while porting FG Parts:" + e.getRootCause() );
                throw e;
            }

            if ( fileList[p].getName() != null && fileList[p].getName().trim().startsWith( SalesAdaptor.FILE_NAME_BACKLOG ) )
            {
                log ( "Parsing Sales backlog File:"  + fileList[p].getName() );
                System.out.println ( "Parsing Sales Backlog File:"  + fileList[p].getName());

                lineItemList = parseSalesFile ( fileList[p], BACKLOG_SHEET );

				//System.out.println("Lineitem list " + lineItemList.size() );

                log ( "Parsing completed successfully..." );
                System.out.println ( "Parsing completed successfully.....");
                //Collections.sort( lineItemList, new SalesParserLineItemComparator() );
                log ( "Porting Sales Backlog info..." );
                System.out.println ( "Porting Sales Backlog info..." );
                DBTransaction dbTransaction = null;

                try
                {
                    dbTransaction = DBTransaction.getInstance();
                    dbTransaction.begin();
                    SalesParserDAO.saveSalesOrdersSiTime( dbTransaction, lineItemList );
                    if ( dbTransaction != null )
                    {
                        dbTransaction.commit();
                    }
                    successFileList.add( fileList[p] );
                    success = true;
                }
                catch ( ScalarException e )
                {
                    log ( "Exception while porting Sales Backlog Orders:" + e.getRootCause() );
                    throw e;
                }
                finally
                {
                    if ( dbTransaction != null )
                    {
                        if ( !success )
                        {
                            try { dbTransaction.rollback(); } catch ( DBXException ignore ){}
                        }
                        dbTransaction = null;
                    }
                }
            }
        }// END OF FOR LOOP

        String fromEmail = ConfigManager.getScalarAdminEmail();
        ArrayList<String> emailList = NotificationDAO.loadEmailsByNotificationType(null, Constants.SALES_ORDER_PORTING_NOTIFICATION );
        String[] emails = new String[emailList.size()];
        emailList.toArray(emails);
        String toEmails = StringUtils.concatStrings( emails, Mailer.EMAIL_SEPARATOR );
        log ( "Sending alerts to: " + toEmails );
        
        if ( success ) //SEND PORTING STATUS MAIL
        {
            log ( "Porting Sales Backlog completed successfully...." );
            System.out.println ( "Porting Sales Backlog completed successfully...." );
            log ( "Sending porting status mail..." );
            portingStatusMsg = preparePortingStatusMsg(lineItemList);
            try
            {
                StringBuffer msg = new StringBuffer ( );
                msg.append ( "<HTML><HEAD><TITLE>Sales Order Porting Status</TITLE></HEAD>" );
                msg.append ( "<BODY ALIGN='CENTER'><TABLE STYLE='FONT-SIZE: 8PT; COLOR: BLACK; FONT-FAMILY: VERDANA' BORDERCOLOR='#FFFFFF' CELLSPACING='1' CELLPADDING='3' BORDER=0 ALIGN='CENTER'>" );
                msg.append ( "<TR BGCOLOR='#CCCCFF'><TD COLSPAN='8' ALIGN='CENTER'><B>SALES ORDER PORTING STATUS REPORT</B></TD></TR>" );
                msg.append ( "<TR BGCOLOR='#D3D3D6' ALIGN='CENTER'>" );
                msg.append ( "<TD><B>S.No</B></TD><TD><B>SALES ORDER #</B></TD><TD><B>CUSTOMER NAME</B></TD><TD><B>LINE ITEM #</B></TD><TD><B>Quantity</B></TD><TD><B>PRODUCT</B></TD><TD><B>PORTING STATUS</B></TD><TD><B>REMARKS</B></TD>" );
                msg.append ( "</TR>" );
                msg.append ( portingStatusMsg );
                msg.append ( "</TABLE></BODY></HTML>" );

                //System.out.println(msg.toString());
                //log(msg.toString());

                sendSalesOrdersPortingAlert( "Sales Order porting status alert", msg.toString(), fromEmail, toEmails );

                log ( "Porting status mail sent successfully" );
                System.out.println ( "Porting status mail sent successfully" );
            }
            catch ( ScalarException e )
            {
                log ( "**WARNING** Could not send Porting Status alert mail:" + e.getRootCause() );
                System.out.println ( "***WARNING*** Could not send Porting Status alert mail" );
                e.printStackTrace();
            }
        }
        else
        {
            log ( "Unable to port the BackLog information...." );
            System.out.println ( "Unable to port the BackLog information..." );
        }

        if (fgPartporting && !errorsMap.isEmpty() )
        {
            try
            {
                StringBuffer msg = new StringBuffer ( );
                msg.append ( "<HTML><HEAD><TITLE>FG Parts Porting Status</TITLE></HEAD>" );
                msg.append ( "<BODY ALIGN='CENTER'><TABLE STYLE='FONT-SIZE: 8PT; COLOR: BLACK; FONT-FAMILY: VERDANA' BORDERCOLOR='#FFFFFF' CELLSPACING='1' CELLPADDING='3' BORDER=0 ALIGN='CENTER'>" );
                msg.append ( "<TR BGCOLOR='#CCCCFF'><TD COLSPAN='3' ALIGN='CENTER'><B>FG PARTS PORTING STATUS REPORT</B></TD></TR>" );
                msg.append ( "<TR BGCOLOR='#D3D3D6' ALIGN='CENTER'>" );
                msg.append ( "<TD><B>S.No</B></TD><TD><B>PART NUMBER</B></TD><TD><B>REMARKS</B></TD>" );
                msg.append ( "</TR>" );

                 Iterator it = errorsMap.keySet().iterator();
                 int index      = 1;
                 while( it.hasNext() )
                 {
                     String key = (String)it.next();
                     //System.out.println("key = " + key);
                     String partNo = errorsMap.get( key );

                    msg.append("<TR bgColor='#e6e6e6'>");
                    msg.append("<TD width='3%' align='CENTER'>" + (index++) + ".</TD>");
                    msg.append("<TD width='22%' align='CENTER'>" + partNo + "</TD>");
                    msg.append("<TD width='25%' align='CENTER'>" + key.substring(0, key.indexOf(':')) + "</TD>");
                 }
                 msg.append( "</TR></TABLE><BODY></HTML>\n" );

                //System.out.println(msg.toString());
                //log(msg.toString());

                sendSalesOrdersPortingAlert( "FG Parts porting status alertt", msg.toString(), fromEmail, toEmails );

                log ( "FG parts Porting status mail sent successfully" );
                System.out.println ( " FG parts Porting status mail sent successfully" );
            }
            catch ( ScalarException e )
            {
                log ( "**WARNING** Could not send FG parts Porting Status alert mail:" + e.getRootCause() );
                System.out.println ( "***WARNING*** Could not send FG parts Porting Status alert mail" );
                e.printStackTrace();
            }
        }


        int adaptor_id = AdaptorRunInfo.getLastRunAdaptorId(SalesAdaptor.ADAPTOR_NAME, CONSTANT_ADAPTOR);

        if ( success )
        {
            AdaptorRunInfo.saveAdaptorLastUpdatedStatus(CONSTANT_PARSER, SalesAdaptor.ADAPTOR_NAME, CONSTANT_SUCCESS, adaptor_id);
            System.out.println( "SUCCESSFULLY UPDATED IN THE DATABASE" );
        }
        if ( success )
        {
            AdaptorUtils.moveFilesToDone( successFileList , doneDestDir );
        }
    }

    private void log( String message )
    {
        if ( message == null ) return;

        if ( m_parserLogger != null )
        {
            m_parserLogger.logDebug( message + LINE_SEPARATOR );
        }
        else
        {
            System.out.println( message );
        }
    }

    private ArrayList<SalesParserLineItem> parseSalesFile ( File salesPortingFile, String sheetName ) throws IOException, BiffException
    {
        SalesParserLineItem lineItem = null;
        ArrayList<SalesParserLineItem> lineItemList      = new ArrayList<SalesParserLineItem>();
        ParserResult result            = null;

        try
        {
	        Parser parser = ParserFactory.getXLSParser( salesPortingFile );
	
	        ((XLSParserImpl)parser).setHeaderRowIdx(0);
	        ((XLSParserImpl)parser).setDataStartRowIdx(1);
	
	        System.out.println( "\nFound " + salesPortingFile );
	        System.out.println( "\nParsing the file for sales order ..." );
	
	        result = parser.parse();
            while ( result.next() )
            {
				if( result.getString("Document Number") != null && result.getString("Document Number").trim().length() > 0 )
				{
					lineItem = new SalesParserLineItem();
					lineItem.setPortingStatusCode( 0 );
					lineItem.setSalesOrderNo( result.getString("Document Number").trim() );
					//System.out.println("Result " + result.getString("Customer Name").trim() );
					lineItem.setCustomerName( result.getString("Customer Name").trim() );
					lineItem.setManufacturingPartNo( result.getString("Item Number").trim() );
					lineItem.setQuantity( result.getInt( "QTY" ) );
					lineItem.setUnitPrice( new Double(result.getDouble( "Unit Price" )).floatValue() );
					lineItem.setBookingDate( StringUtils.formatDate( result.getString( "Document Date" ), "MM/dd/yy", "MM/dd/yyyy" ) );
					lineItem.setRequestedDockDate( StringUtils.formatDate( result.getString( "Commit Date Line Item"), "MM/dd/yy", "MM/dd/yyyy" ) );
					lineItem.setScheduleDate( StringUtils.formatDate( result.getString( "Commit Date Line Item"), "MM/dd/yy", "MM/dd/yyyy" ) );
					//lineItem.setBookingDate( StringUtils.formatDateToString( result.getDate( "Document Date" ), "MM/dd/yyyy HH:mm:ss" ) );
					//lineItem.setRequestedDockDate( StringUtils.formatDateToString( result.getDate( "Commit Date Line Item"), "MM/dd/yyyy HH:mm:ss" ) );
					//lineItem.setScheduleDate( StringUtils.formatDateToString( result.getDate( "Commit Date Line Item"), "MM/dd/yyyy HH:mm:ss" ) );
					lineItem.setEndCustomer( result.getString( "Reference" ) );
					lineItem.setCustomerPO( result.getString( "Customer PO Number" ) );
					lineItem.setShippingMethod( result.getString( "Comment 1" ) );
					lineItem.setStatus( result.getString( "Comment 2" ) );
					lineItem.setLineItemNo( result.getInt( "Line Item Sequence" ) );
					lineItem.setPaymentTerms( result.getString( "Payment Terms ID" ) );
					Address shipAddress = new Address();
					shipAddress.setAddress1( result.getString( "ShipToName from Sales Line Item" ) );
					shipAddress.setAddress2( result.getString( "Address 1 from Sales Line Item" ) );
					shipAddress.setAddress3( result.getString( "Address 2 from Sales Line Item" ) );
					shipAddress.setAddress4( result.getString( "Address 3 from Sales Line Item" ) );
					shipAddress.setCity( result.getString( "City from Sales Line Item" ) );
					shipAddress.setState( result.getString( "State from Sales Line Item" ) );
					shipAddress.setZipCode( result.getString( "Zip Code from Sales Line Item" ) );
					shipAddress.setCountry( result.getString( "Country from Sales Line Item" ) );
					shipAddress.setContactPerson( result.getString( "Contact Person from Sales Line Item" ) );
					shipAddress.setPhoneNumber( result.getString( "Phone 1 from Sales Line Item" ) );
					shipAddress.setTagName( result.getString( "Primary Shipto Address Code from Sales Line Item" ) );
					lineItem.setShipAddress( shipAddress );
					
					Address billingAddress = new Address();
					billingAddress.setAddress1( lineItem.getCustomerName() );
					billingAddress.setAddress2( result.getString( "Address 1 from Customer Master" ) );
					billingAddress.setAddress3( result.getString( "Address 2 from Customer Master" ) );
					billingAddress.setAddress4( result.getString( "Address 3 from Customer Master" ) );
					billingAddress.setCity( result.getString( "City from Customer Master" ) );
					billingAddress.setState( result.getString( "State from Customer Master" ) );
					billingAddress.setCountry( result.getString( "Country from Customer Master" ) );
					billingAddress.setTagName( result.getString( "Primary Billto Address Code" ) );
					//billingAddress.setContactPerson( result.getString( "Contact Person from Sales Line Item" ) );
				   // billingAddress.setPhoneNumber( result.getString( "Contact Person from Sales Line Item" ) );
					lineItem.setBillingAddress( billingAddress );
					
					lineItem.setCustomerNumber( result.getString( "Customer Number" ) );
					
					lineItemList.add( lineItem );
				}
            }
        }
        catch( Exception e)
    	{
        	e.printStackTrace();        	
    	}
        return lineItemList;
    }
    public static void main ( String[] args ) throws Exception
    {
        SalesParser salesParser = new SalesParser();
        salesParser.execute();
    }

    private static HashMap<String, String> createFGParts( String fileName ) throws Exception
    {
        if ( fileName == null ) throw new ScalarException( "Filename argument is null" );

        ParserResult result     = null;
        Connection conn         = null;
		PreparedStatement pstmt = null;
        HashMap<String, String> errorsMap = new HashMap<String, String>();
        boolean success = false;

        conn = DataSource.getConnection();

        File detailWipFile = new File( fileName );
        if ( !detailWipFile.exists() )
        {
            throw new ScalarException( fileName + " doesnt exist" );
        }

        Parser parser = ParserFactory.getXLSParser( detailWipFile );

        ((XLSParserImpl)parser).setHeaderRowIdx(0);
        ((XLSParserImpl)parser).setDataStartRowIdx(1);
        try
        {
            pstmt = conn.prepareStatement( " { CALL SP_LOAD_FG_PARTS (?, ? ) }" );
            result = parser.parse();
            //System.out.println("result = " + result.getHeadings() );
            while ( result.next() )
            {
                if ( result.getString ( "Item Number" ) != null && result.getString ( "Item Number" ).trim().length() > 0  )
                {
                    pstmt.setString( 1, result.getString ( "Item Number" ).trim() );
                    pstmt.setString( 2, result.getString ( "Item Description" ) );

                    try
                    {
                        pstmt.execute();
                    }
                    catch( SQLException sqlEx )
                    {
                        errorsMap.put( sqlEx.getMessage(),  result.getString ( "Item Number" ).trim() );
                    }
                }
            }
            //pstmt.executeBatch();

            success = true;
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        finally
        {

            try
            {
                if (conn != null)
                {
                    if( success == true )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {System.out.println("Sql exception ");}

            if ( pstmt != null )
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
        
        return errorsMap;
    }
    private String preparePortingStatusMsg ( ArrayList<SalesParserLineItem> lineItemList )
    {
        SalesParserLineItem lineItem   = null;
        StringBuffer statusMsg  = new StringBuffer();
        int lineItemListSize = ( lineItemList == null ) ? 0 : lineItemList.size();
        int count               = 0;
        for ( int i = 0; i < lineItemListSize; i++ )
        {
            lineItem = (SalesParserLineItem)lineItemList.get( i );

            if ( StringUtils.isNullOrEmpty( lineItem.getSalesOrderNo() ) && StringUtils.isNullOrEmpty( lineItem.getCustomerName() ) && lineItem.getLineItemNo() == 0 && lineItem.getShipmentNo() == 0 )
            {
                continue;
            }
            statusMsg.append ( "<TR BGCOLOR='#E6E6E6' ALIGN='CENTER'>" );
            statusMsg.append ( "<TD ALIGN='LEFT'>" ).append ( ++count ).append ( "</TD>" );
            statusMsg.append ( "<TD ALIGN='LEFT'>" ).append ( lineItem.getSalesOrderNo() ).append ( "</TD>" );
            statusMsg.append ( "<TD ALIGN='LEFT'>" ).append ( lineItem.getCustomerName() ).append ( "</TD>" );
            statusMsg.append ( "<TD ALIGN='CENTER'>" ).append ( (lineItem.getLineItemNo()/16384) ).append ( "</TD>" );
            statusMsg.append ( "<TD ALIGN='CENTER'>" ).append ( lineItem.getQuantity() ).append ( "</TD>" );
            statusMsg.append ( "<TD ALIGN='CENTER'>" ).append ( lineItem.getManufacturingPartNo() ).append ( "</TD>" );
            switch( lineItem.getPortingStatusCode() )
            {
                case 0: //'SUCCESS' NEW LINE ITEM CREATED
                    statusMsg.append ( "<TD ALIGN='CENTER'>TRUE</TD>" );
                    statusMsg.append ( "<TD ALIGN='LEFT'>New Line Item <B>created</B> successfully</TD>" );
                    break;
                case 1: //'SUCCESS' LINE ITEM UPDATED
                    statusMsg.append ( "<TD ALIGN='CENTER'>TRUE</TD>" );
                    statusMsg.append ( "<TD ALIGN='LEFT'>Line Item <B>updated</B> successfully</TD>" );
                    break;
                case 2: //'FAILED' SALES ORDER NUMBER REQUIRED
                    statusMsg.append ( "<TD ALIGN='CENTER'><B>FALSE</B></TD>" );
                    statusMsg.append ( "<TD ALIGN='LEFT'>Sales Order number required</TD>" );
                    break;
                case 3: //'FAILED' NO FG PART NUMBER FOUND
                    statusMsg.append ( "<TD ALIGN='CENTER'><B>FALSE</B></TD>" );
                    statusMsg.append ( "<TD ALIGN='LEFT'><B>Product</B> not found in the system</TD>" );
                    break;
                case 4: //'FAILED' CUSTOMER NOT FOUND
                    statusMsg.append ( "<TD ALIGN='CENTER'><B>FALSE</B></TD>" );
                    statusMsg.append ( "<TD ALIGN='LEFT'><B>Customer</B> not found in the system</TD>" );
                    break;
                case -1: //'FAILED' At the time of parsing
                    statusMsg.append ( "<TD ALIGN='CENTER'><B>FALSE</B></TD>" );
                    statusMsg.append ( "<TD ALIGN='LEFT'>"+ lineItem.getRemarks() + "</TD>" );
                    break;
            }
            statusMsg.append ( "</TR>");
        }
        return statusMsg.toString();
    }

    private void sendSalesOrdersPortingAlert ( String subject, String message, String fromEmail, String toEmails ) throws Exception
    {

        //System.out.println ( "Sending alerts to: " + toEmails );
        try
        {
            Mailer.send ( fromEmail, toEmails, null, null, subject, message, null, Mailer.CONTENT_TYPE_HTML, false );
        }
        catch( Exception e )
        {
            throw new ScalarException ( "Could not send email notifications", e );
        }
    }
}
